package com.apt.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.apt.common.AptException;
import com.apt.common.DBUtility;
import com.apt.domain.UserTest;
import com.apt.domain.UserTestEval;

public class UserTestEvalDao extends BaseConnection {

	public UserTestEvalDao(Connection conn) {
		super(conn);

	}

	public Integer getLastTestAttemptCount(String userId, int testId)
			throws AptException {
		Integer lastAttemptCount = null;
		try {

			PreparedStatement ps = connection
					.prepareStatement("SELECT TEST_ATTEMPT_NO from  USER_TEST_EVAL WHERE TEST_ID = ? and USER_ID = ?  ORDER BY TEST_ATTEMPT_NO ");
			ps.setInt(1, testId);
			ps.setString(2, userId);

			ResultSet rs = ps.executeQuery();
			List<Integer> attempts = new ArrayList<Integer>();
			while (rs.next()) {

				attempts.add(rs.getInt(1));

			}

			rs.close();
			int total = attempts.size();

			if (total == 0) {

				return null; // No Last Attempt

			}

			lastAttemptCount = new Integer(attempts.get(total - 1));

		} catch (SQLException sqlEx) {
			sqlEx.printStackTrace();
			throw new AptException(
					"Unable to query the database to get the latest test attempt number for userId "
							+ userId + " and testId " + testId);

		} finally {
			DBUtility.cleanUp(connection);

		}

		return lastAttemptCount;

	}

	public Integer insertAndGetTestEvalId(UserTestEval testEval)
			throws AptException {
		Integer testEvalId = null;
		try {

			PreparedStatement ps = connection
					.prepareStatement("INSERT INTO USER_TEST_EVAL( TEST_ID,USER_ID,QUESTIONS_ATTEMPTED,QUESTIONS_CORRECT,"
							+ "QUESTION_INCORRECT,PERCENTAGE,TEST_ATTEMPT_NO) VALUES(?,?,?,?,?,?,?)");
			ps.setInt(1, testEval.getTestId());
			ps.setString(2, testEval.getUserId());
			ps.setInt(3, testEval.getAttempted());
			ps.setInt(4, testEval.getCorrect());
			ps.setInt(5, testEval.getIncorrect());
			ps.setDouble(6, testEval.getPercentage());
			ps.setInt(7, testEval.getAttemptNo());
			ps.executeUpdate();
			connection.commit();
			ps = connection
					.prepareStatement("SELECT USER_TEST_EVAL_ID FROM USER_TEST_EVAL WHERE "
							+ "USER_ID = ? AND TEST_ID = ? AND TEST_ATTEMPT_NO = ? ");
			ps.setString(1, testEval.getUserId());
			ps.setInt(2, testEval.getTestId());
			ps.setInt(3, testEval.getAttemptNo());
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				testEvalId = new Integer(rs.getInt(1));

			}

			return testEvalId;

		} catch (SQLException sqlEx) {
			sqlEx.printStackTrace();
			throw new AptException(
					"Unable to insert the test results for userId "
							+ testEval.getUserId() + " and testId "
							+ testEval.getTestId());

		} finally {

			DBUtility.cleanUp(connection);

		}

	}

	

}
